package stock;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import net.sf.json.JSON;
import net.sf.json.JSONObject;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.xmlbeans.impl.jam.mutable.MPackage;


/**
 * 读取excel
 * @author zlj
 *
 */
@SuppressWarnings("all")
public class ReadExcelUtils {
	
	/**
	 * 判断文件类型
	 * @param file
	 * @return
	 * @throws IOException
	 */
	 public static List<List<String>> readExcelDatas(File file,String fileName) throws IOException{    
         //String fileName = file.getName();    
         String extension = fileName.lastIndexOf(".")==-1?"":fileName.substring(fileName.lastIndexOf(".")+1);    
         if("xls".equals(extension)){    
        	 return read2003Excel(file);    
         }else if("xlsx".equals(extension)){    
        	 return read2007Excel(file);    
         }else{    
        	 throw new IOException("不支持的文件类型");    
         }    
      }

	 /**
	  * 解析.xls文件
	  * @param file
	  * @return
	  * @throws FileNotFoundException
	  * @throws IOException
	  */
	private static List<List<String>> read2003Excel(File file) throws FileNotFoundException, IOException {
		 List<List<String>> list = new LinkedList<List<String>>();    
         HSSFWorkbook hwb = new HSSFWorkbook(new FileInputStream(file));    
         HSSFSheet sheet = hwb.getSheetAt(0);    
         DataFormatter dataFormatter = new DataFormatter();
         Object value = null;    
         HSSFRow row = null;    
         HSSFCell cell = null;     
         for(int i = sheet.getFirstRowNum();i< sheet.getPhysicalNumberOfRows();i++){    
          row = sheet.getRow(i);   
          HSSFRow rowNum = sheet.getRow(0);
          if (row == null) {
           continue;    
          }    
          List<String> linked = new LinkedList<String>();    
          for (int j = rowNum.getFirstCellNum(); j < rowNum.getLastCellNum(); j++) {    
           cell = row.getCell(j);    
           if (cell == null) {  
        		   linked.add("");        		   
            continue;    
           }    
          
           DecimalFormat df = new DecimalFormat("0");// 格式化 number String 字符    
           SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串    
           SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM-dd");// 格式化日期字符串    
           DecimalFormat nf = new DecimalFormat("0");// 格式化数字   
           value = dataFormatter.formatCellValue(cell);
          /* switch (cell.getCellType()) {    
           case XSSFCell.CELL_TYPE_STRING:    
          //  System.out.println(i+"行"+j+" 列 is String type");    
            value = cell.getStringCellValue();    
            break;    
           case XSSFCell.CELL_TYPE_NUMERIC:    
         //   System.out.println(i+"行"+j+" 列 is Number type ; DateFormt:"+cell.getCellStyle().getDataFormatString());    
            if("@".equals(cell.getCellStyle().getDataFormatString())){    
               value = df.format(cell.getNumericCellValue());    
            } else if("General".equals(cell.getCellStyle().getDataFormatString())){    
               //value = nf.format(cell.getNumericCellValue());  
            	value = cell.getNumericCellValue();
            }else{  
            	if("m/d/yy h:mm".equals(cell.getCellStyle().getDataFormatString())) {
            		value = sdf1.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));    
            	}else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
            		value = sdf2.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));    
            	}else {
            		value = sdf2.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
            	}
            }  
            break;    
           case XSSFCell.CELL_TYPE_BOOLEAN:    
         //   System.out.println(i+"行"+j+" 列 is Boolean type");    
            value = cell.getBooleanCellValue();    
            break;    
           case XSSFCell.CELL_TYPE_BLANK:    
         //   System.out.println(i+"行"+j+" 列 is Blank type");    
            value = "";    
            break;  
           case XSSFCell.CELL_TYPE_FORMULA:
				if (cell.getCachedFormulaResultType() == 0) {
					//value = nf.format(cell.getNumericCellValue());
					value = cell.getNumericCellValue();
				}
				if (cell.getCachedFormulaResultType() == 1) {
					value = cell.getStringCellValue();
				}
				break;
           default:    
         //   System.out.println(i+"行"+j+" 列 is default type");    
            value = cell.toString();    
           }    */
           if (value == null || "".equals(value)) {  
        	   linked.add("");
            continue;    
           }   
           linked.add(value.toString());      
         }    
          //判断是否为空数据
          boolean flag = false;
			for (int k = 0; k < linked.size(); k++) {
				if (StringUtils.isNotEmpty(linked.get(k))) {
					flag = true;
					break;
				}
			}
          if(flag) {
        	  list.add(linked);            	  
          }
         }    
         return list;   
	}

	/**
	 * 解析.xlsx文件
	 * @param file
	 * @return
	 * @throws FileNotFoundException
	 * @throws IOException
	 */
	private static List<List<String>> read2007Excel(File file) throws FileNotFoundException, IOException {
		 List<List<String>> list = new LinkedList<List<String>>();    
         // 构造 XSSFWorkbook 对象，strPath 传入文件路径    
         XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));    
         // 读取第一章表格内容    
         XSSFSheet sheet = xwb.getSheetAt(0);    
         Object value = null;    
         XSSFRow row = null;    
         XSSFCell cell = null;    
		for (int i = sheet.getFirstRowNum(); i < sheet.getPhysicalNumberOfRows(); i++) {   
          row = sheet.getRow(i); 
          XSSFRow rowNum = sheet.getRow(0);   
          if (row == null) {    
           continue;    
          }    
          List<String> linked = new LinkedList<String>();    
          for (int j = rowNum.getFirstCellNum(); j < rowNum.getLastCellNum(); j++) {    
           cell = row.getCell(j);    
           if (cell == null) {  
        	   linked.add("");	
        	   continue;    
           }    
           DecimalFormat df = new DecimalFormat("0");// 格式化 number String 字符    
           SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
           SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM-dd");// 格式化日期字符串    
           DecimalFormat nf = new DecimalFormat("0");// 格式化数字    
           switch (cell.getCellType()) {    
           case XSSFCell.CELL_TYPE_STRING:    
         //   System.out.println(i+"行"+j+" 列 is String type");    
            value = cell.getStringCellValue();    
            break;    
           case XSSFCell.CELL_TYPE_NUMERIC:    
         //   System.out.println(i+"行"+j+" 列 is Number type ; DateFormt:"+cell.getCellStyle().getDataFormatString());    
            if("@".equals(cell.getCellStyle().getDataFormatString())){    
              value = df.format(cell.getNumericCellValue());    
            } else if("General".equals(cell.getCellStyle().getDataFormatString())){    
              //value = nf.format(cell.getNumericCellValue());   
            	value = cell.getNumericCellValue();
            }else{    
            	if("m/d/yy h:mm".equals(cell.getCellStyle().getDataFormatString())) {
            		value = sdf1.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));    
            	}else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
            		value = sdf2.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));    
            	}else {
            		value = sdf2.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
            	}
            }    
            break;    
           case XSSFCell.CELL_TYPE_BOOLEAN:    
         //   System.out.println(i+"行"+j+" 列 is Boolean type");    
            value = cell.getBooleanCellValue();    
            break;    
           case XSSFCell.CELL_TYPE_BLANK:    
//          System.out.println(i+"行"+j+" 列 is Blank type");    
            value = "";    
            break; 
           case XSSFCell.CELL_TYPE_FORMULA://公式
				if (cell.getCachedFormulaResultType() == 0) {
					value = cell.getNumericCellValue();
					//value = nf.format(cell.getNumericCellValue());
				}
				if (cell.getCachedFormulaResultType() == 1) {
					value = cell.getStringCellValue();
				}
				break;
           default:    
         //   System.out.println(i+"行"+j+" 列 is default type");    
            value = cell.toString();    
           }    
           if (value == null || "".equals(value)) {    
        	   linked.add("");
        	   continue;    
           }    
           linked.add(value.toString());    
          }    
          //判断是否为空数据
          boolean flag = false;
			for (int k = 0; k < linked.size(); k++) {
				if (StringUtils.isNotEmpty(linked.get(k))) {
					flag = true;
					break;
				}
			}
          if(flag) {
        	  list.add(linked);            	  
          }
         }    
         return list;   
	}  
	public static boolean checkExcelTitles(List<List<String>> dataList,String[] names){
		if(dataList == null || dataList.size() <= 0){
			return false;
		}
		List<String> list = dataList.get(0);
		String excelStr = "";
		String tiltes = "";
		for(String s:list){
			excelStr += s;
		}
		for(String s:names){
			tiltes += s;
		}
		if(!tiltes.equals(excelStr)){
			return false;
		}
		return true;
	}
	 public static void main(String[] args) throws IOException  { 
//       excel 导入数据demo
         File file = new File("F:\\23ba2321383b464c844986ef7a7a0d8d.xls");  
         List<List<String>> dataList=readExcelDatas(file,"23ba2321383b464c844986ef7a7a0d8d.xls");  
         for (int i = 0; i < dataList.size(); i++) {
             for (int j = 0; j < dataList.get(i).size(); j++) {
                 System.out.println(dataList.get(i).get(j));
             }
             System.out.println("------------------");
         }
     }  
}
